﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace WinStudentMS
{
    class SqlHelper
    {
        public static readonly string connString = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;//Windows 身份验证

        public static object ExecuteScalar(string sql, params SqlParameter[] paras)
        {
            object obj = null;
            using ( SqlConnection connection = new SqlConnection(connString))
            {
                SqlCommand command = new SqlCommand(sql, connection);
                command.Parameters.Clear();
                command.Parameters.AddRange(paras);
                connection.Open();
                obj = command.ExecuteScalar();//返回第一行第一列的值
                command.Parameters.Clear();
            }
            return obj;
        }
        
        public static DataTable GetDataTable(string sql, params SqlParameter[] paras)
        {
            DataTable dataTable = new DataTable();
            using (SqlConnection connection = new SqlConnection(connString))
            {
                SqlCommand command = new SqlCommand(sql, connection);
                if (paras != null)
                {
                    command.Parameters.Clear();
                    command.Parameters.AddRange(paras);
                }
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = command;
                adapter.Fill(dataTable);
            }
            return dataTable;
        }

        public static int ExecuteNonQuery(string sql, params SqlParameter[] paras)
        {
            int count = 0;
            using (SqlConnection connection = new SqlConnection(connString))
            {
                SqlCommand command = new SqlCommand(sql, connection);
                command.Parameters.Clear();
                command.Parameters.AddRange(paras);
                connection.Open();
                count = command.ExecuteNonQuery();//返回第一行第一列的值
                command.Parameters.Clear();
            }
            return count;
        }

        public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] parameters)
        {
            SqlConnection connection = new SqlConnection(connString);
            try
            {
                connection.Open();
                SqlCommand command = new SqlCommand(sql, connection);
                command.Parameters.Clear();
                command.Parameters.AddRange(parameters);
                SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                return reader;
            }
            catch (SqlException exception)
            {
                connection.Close();
                throw new Exception("执行查询出现异常", exception);
            }
        }

        public static bool ExecuteTrans(List<CommandInfo> commandInfos)
        {
            using (SqlConnection connection = new SqlConnection(connString))
            {
                connection.Open();
                SqlTransaction transaction = connection.BeginTransaction();
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.Transaction = transaction;
                try
                {
                    int count = 0;
                    for (int i = 0; i < commandInfos.Count; i++)
                    {
                        command.CommandText = commandInfos[i].CommandText;
                        if (commandInfos[i].IsProc)
                            command.CommandType = CommandType.StoredProcedure;
                        else
                            command.CommandType = CommandType.Text;
                        if (commandInfos[i].Parameters.Length > 0)
                        {
                            command.Parameters.Clear();
                            command.Parameters.AddRange(commandInfos[i].Parameters);
                        }
                        count += command.ExecuteNonQuery();
                        command.Parameters.Clear();
                    }
                    transaction.Commit();
                    return true;
                }
                catch (Exception exception)
                {
                    transaction.Rollback();
                    throw new Exception("执行事务出现异常", exception);
                }
            }
        }
    }
}
